﻿using NLog;
using StackExchange.Redis;
using System.Data;
using TeachlyQBank.DbPool;
using TeachlyQBank.Models;

namespace TeachlyQBank.DbServer
{
    public class QuestionTypeServer
    {
        private readonly IHttpContextAccessor _httpContextAccessor; // 操作cookie中数据的类
        private readonly OracleConnectionAdapter _oracle; // 操作oracle的类
        private readonly IConnectionMultiplexer _redis;  // 操作redis的类
        private readonly Logger _logger;  //记录日志的类

        // 完成依赖注入
        public QuestionTypeServer(OracleConnectionAdapter oracle, IConnectionMultiplexer redis, IHttpContextAccessor httpContextAccessor)
        {
            _httpContextAccessor = httpContextAccessor;
            _redis = redis;
            _logger = LogManager.GetCurrentClassLogger();
            _oracle = oracle;
        }

        // 功能:通过QuestionType的id查询QuestionType 
        // 参数：一个QuestionType类型的数据包含了要查询的id
        // 返回：查询成功返回目标QuestionType,失败返回null
        public QuestionType SelectById(QuestionType questionType)
        {
            _oracle.Open();

            string query = "SELECT * FROM T_QUESTION_TYPE WHERE qt_id = @qt_id";
            Dictionary<string, object> parameters = new Dictionary<string, object>
            {
                { "@qu_id",questionType.qt_id.ToString() }
            };

            DataSet data = _oracle.ExecuteQuery(query, parameters);
            _oracle.Close();

            if (data == null || data.Tables[0].Rows.Count == 0)
            {
                // 查询出现错误
                _logger.Error($"查询出错，错误的SQL语句：{_oracle.GetLastExecutedSql()}，错误的信息：{_oracle.GetLastError()}");
                return null;
            }

            DataRow row = data.Tables[0].Rows[0];
            QuestionType result = new QuestionType
            {
                qt_id = row["QT_ID"].ToString(),
                type_name = row["TYPE_NAME"].ToString(),
                type_desc = row["TYPE_DESC"]?.ToString(),
                type_score = row["TYPE_SCORE"] as int?,
                kyeid = Convert.ToInt32(row["KEYID"])
            };
            return result;
        }

        // 功能:插入一个新的QuestionType到数据库中
        // 参数：一个QuestionType类型的数据包含了要插入的信息
        // 返回：插入成功返回受影响的行数(应为1)，失败返回0或负值
        public int Insert(QuestionType questionType)
        {
            _oracle.Open();


            string query = "INSERT INTO T_QUESTION_TYPE(QT_ID, TYPE_NAME, TYPE_DESC, TYPE_SCORE) VALUES (:qt_id, :type_name, :type_desc, :type_score)";

            Dictionary<string, object> parameters = new Dictionary<string, object>
            {
                    { ":qt_id", questionType.qt_id },
                    { ":type_name", questionType.type_name },
                    { ":type_desc", questionType.type_desc },
                    { ":type_score", questionType.type_score }
             };


            int result = _oracle.ExecuteNonQuery(query, parameters);

            if (result <= 0)
            {
                // 插入出现错误
                _logger.Error($"插入出错，错误的SQL语句：{_oracle.GetLastExecutedSql()}，错误的信息：{_oracle.GetLastError()}");
            }

            _oracle.Close();

            return result;
        }

        // 功能:查询所有的QuestionType
        // 参数:不需要参数
        // 返回：执行成功返回一个List<QuestionType>里面包含了所有的QuestionType的数据
        // 注意: 这里最好是要做分页处理,不过暂时不做
        public List<QuestionType> GetAllQuestionTypes()
        {
            _oracle.Open();

            string query = "SELECT * FROM T_QUESTION_TYPE";

            DataSet data = _oracle.ExecuteQuery(query);
            if (data == null || data.Tables[0].Rows.Count == 0)
            {
                _oracle.Close();
                // 查询出先错误
                _logger.Error($"查询所有QuestionType出错了,错误的sql语句:{_oracle.GetLastExecutedSql()},错误的信息:{_oracle.GetLastError()}");
                return null;
            }
            else
            {
                List<QuestionType> resultList = new List<QuestionType>();

                foreach (DataRow row in data.Tables[0].Rows)
                {
                    QuestionType questionType = new QuestionType();

                    questionType.qt_id = row["QT_ID"].ToString();
                    questionType.type_name = row["TYPE_NAME"].ToString();
                    questionType.type_desc = row["TYPE_DESC"]?.ToString();
                    questionType.type_score = int.Parse(row["TYPE_SCORE"].ToString());
                    resultList.Add(questionType);
                }
                _oracle.Close();
                return resultList;
            }
        }

        public List<QuestionType> GetScoreById(string questionTypeId)
        {
            _oracle.Open();

            string query = "SELECT  *  FROM T_QUESTION_TYPE WHERE QT_ID=:questionTypeId";
            Dictionary<string, object> parameters = new Dictionary<string, object>
            {
                    { ":questionTypeId", questionTypeId },
             };

            DataSet data = _oracle.ExecuteQuery(query,parameters);
            if (data == null || data.Tables[0].Rows.Count == 0)
            {
                _oracle.Close();
                // 查询出先错误
                _logger.Error($"查询所有QuestionType出错了,错误的sql语句:{_oracle.GetLastExecutedSql()},错误的信息:{_oracle.GetLastError()}");
                return null;
            }
            else
            {
                List<QuestionType> resultList = new List<QuestionType>();

                foreach (DataRow row in data.Tables[0].Rows)
                {
                    QuestionType questionType = new QuestionType();

                    questionType.qt_id = row["QT_ID"].ToString();
                    questionType.type_name = row["TYPE_NAME"].ToString();
                    questionType.type_desc = row["TYPE_DESC"]?.ToString();
                    questionType.type_score = int.Parse((row["TYPE_SCORE"]).ToString());

                    resultList.Add(questionType);
                }
                _oracle.Close();
                return resultList;
            }
        }
    }
}
